package cn.vw.hi.commen.persistence;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Expression;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import cn.vw.hi.commen.model.City;
import cn.vw.hi.commen.model.Counties;
import cn.vw.hi.commen.model.Loads;
import cn.vw.hi.commen.model.Province;
import cn.vw.hi.commen.tools.Helper;

public class DicDaoImpl extends HibernateDaoSupport implements DicDao {

	//获得省列表
	public List provinceList() {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				Criteria criteria=session.createCriteria(Province.class);
				return criteria.list();
			}
		});
		return list;
	}

	//获得市列表
	public List cityList(final String provinceId) {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				Criteria criteria=session.createCriteria(City.class);
				criteria.add(Expression.eq("provinceId",provinceId));
				return criteria.list();
			}
			
		});
		return list;
	}

	//获得区列表
	public List countiesList(final String cityId) {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				Criteria criteria=session.createCriteria(Counties.class);
				criteria.add(Expression.eq("cityId",cityId));
				return criteria.list();
			}
		});
		return list;
	}

	//添加路
	public boolean addLoad(Loads loads) {
		try
		{
		  this.getHibernateTemplate().save(loads);
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}

	//获得路的列表
	public List loadsList(final String provinceId,final  String cityId,final String countiesId) {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				Criteria criteria=session.createCriteria(Loads.class);
				criteria.add(Expression.eq("provinceId",provinceId));
				if(!cityId.equals("-1"))
				{
				    criteria.add(Expression.eq("cityId",cityId));
				}
				if(!countiesId.equals("-1"))
				{
					criteria.add(Expression.eq("countiesId",countiesId));
				}
				return criteria.list();
			}
			
		});
		return list;
	}

	//获得路的列表by latlng
	public List loadListByLatLng(final String latlng,final String searchStr,final int radius) {
		List list=(List)this.getHibernateTemplate().execute(new HibernateCallback(){
			public Object doInHibernate(Session session)throws HibernateException, SQLException {
				String[] latlngStr=latlng.split(",");
				String sql="from Loads l where round(((abs((l.endLat* 3.1415926 / 180.0 - l.startLat* 3.1415926 / 180.0) * "+latlngStr[0]+"+(l.startLng - l.endLng) * "+latlngStr[1]+"* 3.1415926" +
						" / 180.0 + ((l.endLng * l.startLat*3.1415926 / 180.0) -(l.startLng * l.endLat*3.1415926 / 180.0)))) / (sqrt(pow(l.endLat - l.startLat, 2) " +
						"+ pow(l.startLng - l.endLng, 2))) * 6378137)*10000)/10000<"+radius+" and l.provinceId='"+searchStr.split("_")[0]+"' and l.cityId='"+searchStr.split("_")[1]+"'";
				Query query=session.createQuery(sql);
				return query.list();
			}
		});
		String[] latlngstr=latlng.split(",");
		List temp=new ArrayList();
		for(int i=0;i<list.size();i++)
		{
			Loads loads=(Loads)list.get(i);
			if(Helper.pointToLine(loads.getStartLng(), loads.getStartLat(), loads.getEndLng(),loads.getEndLat(),Double.parseDouble(latlngstr[0]), Double.parseDouble(latlngstr[1]))<=radius)
			{
				temp.add(loads);
			}
		}
		return temp;
	}

	//查找路
	public Loads findLoads(Integer id) {
		Loads loads=(Loads)this.getHibernateTemplate().get(Loads.class, id);
		return loads;
	}

	//修改路
	public boolean modifyLoads(Loads loads) {
		try
		{
		Loads l=(Loads)this.getHibernateTemplate().get(Loads.class, loads.getId());
		BeanUtils.copyProperties(l,loads);
		this.getHibernateTemplate().update(l);
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}

	//删除路
	public boolean deleteLoads(final String ids) {
		try
		{
		  this.getHibernateTemplate().execute(new HibernateCallback(){

			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				Query query=session.createQuery("delete from Loads l where l.id in("+ids+")");
				query.executeUpdate();
				return null;
			}
		  });
		}catch(Exception e)
		{
			e.printStackTrace();
			return false;
		}
		return true;
	}

}
